import json

import pandas as pd
import geopandas as gpd
import matplotlib
import matplotlib.pyplot as plt
from aibox.data.cache_tool import RedisCache
from shapely.geometry import Point
from bqtool.test.csv_to_db import set
from bqtool.utils.tools import get_db


def get_data():
    tgs = pd.read_sql('t_itgs_tgsinfo', get_db('ehl_hbgs', 'ehl1234', '10.2.111.66'))
    tgs = tgs.dropna(subset=['x','y'])
    tgs['location'] = [Point(xy) for xy in zip(pd.to_numeric(tgs['x']), pd.to_numeric(tgs['y']))]
    tgs = gpd.GeoDataFrame(tgs, geometry='location', crs={'init': 'epsg:4326'})
    jsonData = tgs[['kkid','kkmc','cloud_id','location']].to_json()
    # save_json("tgs.json",jsonData)
    path = "E:\EHL\CODE\python\项目\河北\河北高速总队大数据资源平台\图层\FWQ_PT.shp"
    sfz = gpd.read_file(path, encoding='gbk',crs={'init': 'epsg:4326'})
    save_json("fwq.json",sfz[['geometry','XZQHBH','MC']].to_json())
    return ''

def save_json(file,jsonData):
    with open(file, "w") as f:
        json.dump(json.loads(jsonData), f)
        print("加载入文件完成...")


def get_shp_data(db,sql,x,y,columns,filePath,save=False):
    tgs = pd.read_sql(sql, db)
    tgs = tgs.dropna(subset=[x,y])
    tgs['location'] = [Point(xy) for xy in zip(pd.to_numeric(tgs[x]), pd.to_numeric(tgs[y]))]
    tgs = gpd.GeoDataFrame(tgs, geometry='location', crs={'init': 'epsg:4326'})
    tgs['up_time'] = tgs['up_time'].dt.strftime('%Y-%m-%d %H:%M:%S')
    if save:
        jsonData = tgs[columns].to_json()
        save_json(filePath,jsonData)
    return tgs

def read_gis_data(gispl, mod_conf, encoding=None, buffer=False):
    # config_path = os.path.split(os.path.dirname(__file__))[0]
    array = []
    # mod_conf = add_mod_conf()
    for plstr in gispl:
        pl = gpd.read_file(mod_conf + plstr + "/" + plstr + ".shp", encoding=encoding)
        array.append(pl)
    frame = pd.concat(array)
    gis_data = frame
    if buffer:
        gis_data.geometry = gis_data.geometry.buffer(0.1/6371.393)
    return gis_data

def read_shp(data):
    gis_df = read_gis_data(['R_GS_PL', 'R_GD_PL', 'R_SD_PL', 'R_CSKSL_PL', 'R_CSZGD_PL'], 'E:/EHL/CODE/python/项目/石家庄交管局/资源/hbgis/',encoding='gbk')
    gis_df.to_pickle('s1')
    gis_df.geometry = gis_df.geometry.buffer(0.1 / 6371.393)
    on_road = gpd.sjoin(data, gis_df)
    print('ok')



if __name__ == '__main__':
    # 冀JY0938
    data = get_shp_data(get_db('ehl_ew', 'ehl1234', '10.2.111.66'),
                 """
                     select * from T_EW_GPS t where t.plate_num='冀AV6519'  order by t.up_time
                     """,
                 'longitude','latitude',
                 ['plate_num','up_time','longitude','latitude','location'],"C:/Users/ehl0060/Desktop/point1.json")
    read_shp(data)
    print('ok')
